package dk.mga.androidExplore;

import java.io.BufferedInputStream;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.StringTokenizer;
import java.util.zip.Deflater;
import java.util.zip.ZipEntry;
import java.util.zip.ZipInputStream;
import java.util.zip.ZipOutputStream;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class SQLiteBackupHandler {
	
	public static interface ProgressListener {
		/**
		 * Call back method with progress indicator
		 * @param pctDone percent done until now.
		 */
		public void progress(int pctDone);
	}

	private final String tag = "myLog";
	// SQL command to get all DB tables (data dictionary ?) (show tables does it for MySql)
	private String sqlCommand = "SELECT * FROM sqlite_master";
	private final int _TYPE = 0;
	private final int _TABLE_NAME_COL = 2;
	private final int _SQL_COMMAND_COL = 4;
	
/**
 * Export database to file and zip it.
 * @param sqloh SQLiteOpenHelper instance representing the target database.
 * @param path Path where zip file is stored.
 * @param outFile output file
 * @param progressListener Progress listener
 * @throws IOException
 */
	public void exportToZipFile(SQLiteOpenHelper sqloh, String path, String outFile, ProgressListener progressListener) throws IOException {
		long a=0,b=0,c=0;
		int jobEstimate = estimateJob(sqloh);
		int jobCnt = 0;
		a = System.currentTimeMillis();
		Cursor tableCur = sqloh.getReadableDatabase().rawQuery(sqlCommand, null);
		StringBuilder sb = new StringBuilder(10000);
		while (tableCur.moveToNext()) { // while more tables
			if ("table".compareTo(tableCur.getString(_TYPE)) == 0) {
				Log.d(tag, "Exporting table: "+ tableCur.getString(_TABLE_NAME_COL));
	 //			SELECT * FROM sqlite_sequence: locationTabel
	 //			Pragma Col names: cid name type notnull dflt_value pk
				sb.append("DROP TABLE IF EXISTS "+tableCur.getString(_TABLE_NAME_COL)+";\n");
				Cursor rowCur = sqloh.getReadableDatabase().rawQuery("select * from "+tableCur.getString(_TABLE_NAME_COL), null);
				Cursor tblInfoCur = sqloh.getReadableDatabase().rawQuery("PRAGMA table_info("+tableCur.getString(_TABLE_NAME_COL)+")", null);
				String[] colTypes = new String[tblInfoCur.getCount()];
				int typeIndx = tblInfoCur.getColumnIndex("type");
				for (int i =0; tblInfoCur.moveToNext(); i++) {
					StringBuilder typeSB = new StringBuilder();
					typeSB.append(tblInfoCur.getString(typeIndx));
					colTypes[i] = typeSB.toString();
				}
				sb.append(tableCur.getString(_SQL_COMMAND_COL)+";");
				while (rowCur.moveToNext()) { // while more rows
					sb.append("\nINSERT INTO "+tableCur.getString(_TABLE_NAME_COL)+" VALUES (");
					if (colTypes[0].toUpperCase().contains("TEXT")) {
						sb.append("\""+rowCur.getString(0)+"\"");// if type = text enclose text in "..."
					} else {
						sb.append(rowCur.getString(0));
					}
					for (int i = 1; (i < rowCur.getColumnCount()); i++) { // for each item in row (except 1st row)
						if (colTypes[i].contains("TEXT")) {
							sb.append(", \""+rowCur.getString(i)+"\"");// if type = text enclose text in "..."
						} else {
							sb.append(", "+rowCur.getString(i));
						}
					}
					sb.append(");");
					if (progressListener != null) {
						jobCnt +=100; // jobDone in pct = jobCnt/jobEstimate
						if (jobCnt%256 == 0){
							int pct = jobCnt/jobEstimate;
							progressListener.progress(pct);
//							Log.d(tag, "Pct done "+pct);
						}
					}
//					Log.d(tag, "sb.length() "+sb.length()+ " cnt:" + testCnt++);
				}
				sb.append("\n");
			}
		}
		b = System.currentTimeMillis();
//		Log.d(tag, "Totale mollevitten\n"+sb.toString().substring(0, sb.length()/4));
//		Log.d(tag, "2....Totale mollevitten\n"+sb.toString().substring(sb.length()/4, sb.length()/2));
//		Log.d(tag, "3....Totale mollevitten\n"+sb.toString().substring(sb.length()/2, sb.length()*3/4));
//		Log.d(tag, "4....Totale mollevitten\n"+sb.toString().substring(sb.length()*3/4, sb.length()));
		String zipFileName = path.concat(outFile).concat(".zip");
	    ZipOutputStream out = new ZipOutputStream(new FileOutputStream(zipFileName));
	    out.setLevel(Deflater.DEFAULT_COMPRESSION);
	    out.putNextEntry(new ZipEntry(outFile));
	    // 96% done here
		c = System.currentTimeMillis();
    	out.write(sb.toString().getBytes(), 0, sb.toString().getBytes().length);
		Log.d(tag, "% time used from a-b "+(b-a)*100/(c-a));
	    out.closeEntry();
	    out.close();
	}
/**
 * Import database from zip file with
 * @param sqloh
 * @param inFile
 * @param progressListener
 * @throws IOException
 */
	public void importFromZipFile(SQLiteOpenHelper sqloh, String inFile, ProgressListener progressListener) throws IOException {
		Log.d(tag, "test import DB");
		ZipInputStream in = new ZipInputStream(new BufferedInputStream(new FileInputStream(inFile)));
		byte[] buffer = new byte[1028];
		ZipEntry ze;
		while ((ze = in.getNextEntry()) != null) {
			String s = ze.getName();
			Log.d(tag, "Name of zip entry "+s);
			int l;
			StringBuilder sb = new StringBuilder(10000);
			while ((l = in.read(buffer)) > -1) {
				sb.append(new String(buffer, 0, l));
			}
			Log.d(tag, "Totale mollevitten\n"+sb.toString().substring(0, sb.length()/4));
			Log.d(tag, "2....Totale mollevitten\n"+sb.toString().substring(sb.length()/4, sb.length()/2));
			Log.d(tag, "3....Totale mollevitten\n"+sb.toString().substring(sb.length()/2, sb.length()*3/4));
			Log.d(tag, "4....Totale mollevitten\n"+sb.toString().substring(sb.length()*3/4, sb.length()));
			if (sqloh != null) {
				StringTokenizer st = new StringTokenizer(sb.toString(), "\n");
				SQLiteDatabase db = sqloh.getWritableDatabase();
				int jobEstimate = st.countTokens();
				int jobCnt = 0;
				while (st.hasMoreTokens()) {
					String sqlCommand = st.nextToken();
					if (!sqlCommand.contains("sqlite_sequence")) {
						db.execSQL(sqlCommand);
					}
					if (progressListener != null) {
						jobCnt +=100; // jobDone in pct = jobCnt/jobEstimate
						if (jobCnt%256 == 0){
							int pct = jobCnt/jobEstimate;
							progressListener.progress(pct);
						}
					}
				}
			}
		}
	}
	
	private int estimateJob(SQLiteOpenHelper sqloh) {
		int cnt=0;
		Cursor tableCur = sqloh.getReadableDatabase().rawQuery(sqlCommand, null);
		while (tableCur.moveToNext()) { // while more tables
			Cursor cursor = sqloh.getReadableDatabase().rawQuery("SELECT COUNT (*) FROM "+tableCur.getString(_TABLE_NAME_COL)+"", null);
			if (cursor.moveToNext()) {
				cnt += cursor.getInt(0);
			}
		}
		Log.d(tag, "estimated job:"+cnt);
		return cnt;
	}
	
}
